﻿Imports mshtml
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
'test

Public Class CAccesoHTML

#Region "Acceso HTML"

    Public Shared Function ObtenerHTML(ByVal strUrl As String) As String
        Dim WR As System.Net.WebRequest
        Dim Rsp As System.Net.WebResponse
        Try
            WR = System.Net.WebRequest.Create(strUrl)
            Rsp = WR.GetResponse()
            Return New StreamReader(Rsp.GetResponseStream()).ReadToEnd()
        Catch ex As System.Net.WebException
            Throw ex
        End Try
    End Function

    Public Shared Function ObtenerTablasEntexto(ByVal sHTML As String) As List(Of String)

        Dim myDoc As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument
        Dim mElement As mshtml.IHTMLElement
        Dim mElementAux As mshtml.IHTMLElement
        Dim mETablas As mshtml.IHTMLElementCollection


        Dim i As Integer
        Dim lstResults As New List(Of String)


        Try


            myDoc.close()
            myDoc.open("about:blank")

            Dim clsHTML() As Object = {sHTML}
            myDoc.write(clsHTML)
            clsHTML = Nothing
            mElement = myDoc.body()

            mETablas = mElement.getElementsByTagName("table")

            For i = 0 To mETablas.length - 1
                mElementAux = mETablas.item(i)
                lstResults.Add(mElementAux.innerHTML)
            Next

            Return lstResults

        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Shared Function ObtenerRegistros(ByVal sHTML As String) As List(Of String)


        Dim myDocTR As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument
        Dim mElementTR As mshtml.IHTMLElement
        Dim mElementAuxTR As mshtml.IHTMLElement
        Dim mERegistros As mshtml.IHTMLElementCollection
        Dim lstRegistros As New List(Of String)


        Try


            myDocTR.close()
            myDocTR.open("about:blank")


            Dim clsHTMLTR() As Object = {sHTML}
            myDocTR.write(clsHTMLTR)
            clsHTMLTR = Nothing
            mElementTR = myDocTR.body()
            mERegistros = mElementTR.getElementsByTagName("TR")

            For i = 0 To mERegistros.length - 1
                mElementAuxTR = mERegistros.item(i)
                lstRegistros.Add(mElementAuxTR.innerHTML)
            Next

            Return lstRegistros

        Catch ex As Exception
            Throw ex
        End Try





    End Function

    Public Shared Function ObtenerColumnas(ByVal sHTML As String) As List(Of String)

        Dim myDocTD As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument
        Dim mElementTD As mshtml.IHTMLElement
        Dim mElementAuxTD As mshtml.IHTMLElement
        Dim mEColumnas As mshtml.IHTMLElementCollection
        Dim lstColumnas As New List(Of String)


        Try

            myDocTD.close()
            myDocTD.open("about:blank")


            Dim clsHTMLTD() As Object = {sHTML}
            myDocTD.write(clsHTMLTD)
            clsHTMLTD = Nothing
            mElementTD = myDocTD.body()

            mEColumnas = mElementTD.getElementsByTagName("TD")

            For i = 0 To mEColumnas.length - 1
                mElementAuxTD = mEColumnas.item(i)

                If mElementAuxTD.getAttribute("colspan") <> 1 Then
                    lstColumnas.Clear()
                    Exit For
                End If

                If mElementAuxTD.all.length = 0 And Not mElementAuxTD.innerHTML Is Nothing Then
                    lstColumnas.Add(mElementAuxTD.innerHTML.Replace("&nbsp;", " ").Trim())
                Else
                    lstColumnas.Add("")
                End If

            Next

            Return lstColumnas

        Catch ex As Exception
            Throw ex
        End Try



    End Function

    Public Shared Function GenerarTablaVB(ByVal sTablaHTML As String, ByVal sNomtablaVB As String) As DataTable

        Dim sCodigoHTMLTR As String
        Dim sCodigoHTMLTD As String

        Dim lstRegistros As New List(Of String)
        Dim lstColumnas As New List(Of String)


        Dim dt As New DataTable(sNomtablaVB)
        Dim columnaVB As DataColumn
        Dim registroVB As DataRow


        Dim estaDefinidaLaColumna As Boolean = False

        Dim i As Integer


        Try


            '1) Obtener registros de la tabla especificada

            sCodigoHTMLTR = "<html><body><TABLE>" & sTablaHTML & " </TABLE></body></html>"
            lstRegistros = ObtenerRegistros(sCodigoHTMLTR)


            '2) Obtener colunmas


            For Each registro As String In lstRegistros
                sCodigoHTMLTD = "<html><body><TABLE><tr>" & registro & "</tr></TABLE></body></html>"
                lstColumnas = ObtenerColumnas(sCodigoHTMLTD)

                If estaDefinidaLaColumna = False Then
                    If lstColumnas.Count > 0 Then

                        For i = 0 To lstColumnas.Count - 1
                            columnaVB = New DataColumn("columna_" & i, System.Type.GetType("System.String"))
                            dt.Columns.Add(columnaVB)
                        Next

                        estaDefinidaLaColumna = True
                    End If
                End If

                registroVB = dt.NewRow()
                For i = 0 To lstColumnas.Count - 1
                    registroVB(i) = lstColumnas(i)
                Next
                dt.Rows.Add(registroVB)
                lstColumnas.Clear()
            Next


            Return dt


        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Shared Function ObtenerSubConjuntoDeTablas(ByVal indexTablas() As Integer, ByRef conjuntoTablasHTML As List(Of String)) As List(Of DataTable)

        Dim tablasVB As New List(Of DataTable)
        Dim i As Integer

        Try

            For i = 0 To indexTablas.Count - 1
                tablasVB.Add(GenerarTablaVB(conjuntoTablasHTML(indexTablas(i)), "Tabla_" & indexTablas(i)))
            Next

            Return tablasVB

        Catch ex As Exception
            Throw ex
        End Try




    End Function

    Public Shared Function ObtenerValorElementoHTML(ByVal sCodigoHTML As String, ByVal idHTML As String) As String

        Dim myDoc As mshtml.IHTMLDocument2 = New mshtml.HTMLDocument
        Dim mElement As mshtml.IHTMLElement2

        Dim valor As String = ""

        Try

            myDoc.close()
            myDoc.open("about:blank")

            Dim clsHTML() As Object = {sCodigoHTML}
            myDoc.write(clsHTML)
            clsHTML = Nothing
            mElement = myDoc.all.item(idHTML)
            valor = IIf(IsNumeric(mElement.getAttribute("value")), mElement.getAttribute("value"), "")

            Return valor
        Catch ex As Exception
            Return ""
        End Try



    End Function

#End Region


#Region "Proceso Linea ValmerDecalog HTML"

    Public Shared Function ConsultarDatosValmerCalc(ByRef paramDatos As ParamConsultaDatosValmer) As DataTable
        'El formato de la fecha debe ser "yyyy-mm-dd"


        Dim indexTablas(0) As Integer

        Dim sCodigoHTML   As String
        Dim lstTablasHTML As New List(Of String)
        Dim tablasHTMLaVB As New List(Of DataTable)


        Dim sURL As String


        Try


            If paramDatos.serie.Length = 5 And paramDatos.tipoInstDec = "Guber.pl" = True Then
                paramDatos.serie = "0" & paramDatos.serie
            End If



            With paramDatos
                sURL = "http://www.valmer.com.mx/cgi/Calculadora_Instrumentos_" & .tipoInstDec & "?cambio_combo=2&carga_fecha=2&calculo_datos=0&instrumento=&PBedit_chec=&habilitaPSucio=&habilitaRadio=1&b_sobretasa_yield=&" & _
                                "tv=" & .tv & _
                                "&emisora=" & .emisora & _
                                "&serie="   & .serie & _
                                "&radio="   & .radio & _
                                "&fecha="   & .fecha.Substring(0, 4) & "%2F" & .fecha.Substring(5, 2) & "%2F" & .fecha.Substring(8, 2)
            End With


            indexTablas(0) = 9

            sCodigoHTML = ObtenerHTML(sURL)
            lstTablasHTML = ObtenerTablasEntexto(sCodigoHTML)
            tablasHTMLaVB = ObtenerSubConjuntoDeTablas(indexTablas, lstTablasHTML)


            If tablasHTMLaVB(0).Rows.Count = paramDatos.numRegLayout And tablasHTMLaVB(0).Columns.Count = paramDatos.numColLayout Then


                tablasHTMLaVB(0).Rows(0)(1) = ObtenerValorElementoHTML(sCodigoHTML, "sobretasa")
                tablasHTMLaVB(0).Rows(1)(1) = ObtenerValorElementoHTML(sCodigoHTML, "tasaRendimiento")
                tablasHTMLaVB(0).Rows(2)(1) = ObtenerValorElementoHTML(sCodigoHTML, "tasaCupon")
                tablasHTMLaVB(0).Rows(3)(1) = ObtenerValorElementoHTML(sCodigoHTML, "tasaMercado")
                tablasHTMLaVB(0).Rows(4)(1) = ObtenerValorElementoHTML(sCodigoHTML, "isr")
                tablasHTMLaVB(0).Rows(5)(1) = ObtenerValorElementoHTML(sCodigoHTML, "tipoCambioUDI")
                tablasHTMLaVB(0).Rows(6)(1) = ObtenerValorElementoHTML(sCodigoHTML, "tipoCambioUSD")
                tablasHTMLaVB(0).Rows(7)(1) = ObtenerValorElementoHTML(sCodigoHTML, "tasaDescuento")

            End If

            Return tablasHTMLaVB(0)

        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Shared Function ConsultarFlujosValmer(ByRef paramFlujos As ParamFlujosValmer) As List(Of DataTable)

        Dim indexTablas(3) As Integer

        Dim sCodigoHTML   As String
        Dim lstTablasHTML As New List(Of String)
        Dim tablasHTMLaVB As New List(Of DataTable)


        Dim sURL As String


        Try

            With paramFlujos

                sURL = "http://www.valmer.com.mx/cgi/Calculadora_Instrumentos_" & .tipoInstDec & "?cambio_combo=2&carga_fecha=2&calculo_datos=1&instrumento=&PBedit_chec=&habilitaPSucio=&habilitaRadio=1&b_sobretasa_yield=" & _
                         "&tv=" & .tv & _
                         "&emisora=" & .emisora & _
                         "&serie=" & .serie & _
                         "&calcular=" & .calcula & _
                         "&radio=" & .radio & _
                         "&fecha=" & .fecha.Substring(0, 4) & "%2F" & .fecha.Substring(5, 2) & "%2F" & .fecha.Substring(8, 2) & _
                         "&PBedit=" & .pEdit & _
                         "&sobretasa=" & .sobretasa & _
                         "&tasaRendimiento=" & .tasaRendimiento & _
                         "&tasaCupon=" & .tasaCupon & _
                         "&tasaMercado=" & .tasaMercado & _
                         "&isr=" & .isr & _
                         "&tipoCambioUDI=" & .tipoCambioUDI & _
                         "&tipoCambioUSD=" & .tipoCambioUSD & _
                         "&tasaDescuento=" & .tasaDescuento

            End With


            indexTablas(0) = 5
            indexTablas(1) = 6
            indexTablas(2) = 7
            indexTablas(3) = 11


            sCodigoHTML = ObtenerHTML(sURL)
            lstTablasHTML = ObtenerTablasEntexto(sCodigoHTML)
            tablasHTMLaVB = ObtenerSubConjuntoDeTablas(indexTablas, lstTablasHTML)


            tablasHTMLaVB(1)(2)(0) = ObtenerValorElementoHTML(sCodigoHTML, "precioSucio")


            Return tablasHTMLaVB


        Catch ex As Exception
            Throw ex
        End Try


    End Function

    Public Structure ParamConsultaDatosValmer

        Dim tv As String
        Dim emisora As String
        Dim serie As String
        Dim radio As String
        Dim tipoInstDec As String
        Dim fecha As String

        Dim numRegLayout As String
        Dim numColLayout As String

    End Structure

    Public Structure ParamFlujosValmer

        Dim tv          As String
        Dim emisora     As String
        Dim serie       As String
        Dim calcula     As String
        Dim radio       As String
        Dim tipoInstDec As String
        Dim fecha       As String
        Dim pEdit       As String

        Dim sobretasa       As String
        Dim tasaRendimiento As String
        Dim tasaCupon       As String
        Dim tasaMercado     As String
        Dim isr             As String
        Dim tipoCambioUDI   As String
        Dim tipoCambioUSD   As String
        Dim tasaDescuento   As String

    End Structure


#End Region

#Region "LoadInstrument"


    Public Enum TipoEmisora 
        Corporativo   = 1
        Gubernamental = 2
        Bancario      = 3
        Notas         = 4
        VectorValmer  = 5
        Ninguno       = 6
        Eurobonos     = 7
    End Enum

    Public Shared   Function  ObtenerDatosDeValmeEnCSV(ByVal rutaHTML As String, ByVal nombreTabla As String) As DataTable
       
        Dim nomArchivo   As String 
        
        Dim dt           As New DataTable(nombreTabla)
       ' Dim arregloAux() As String 
        


      Try
 
        '1)Se descarga el archivo del sitio Web de Valmer

        nomArchivo = My.Application.Info.DirectoryPath & "\temp_" & nombreTabla  &".csv"
        My.Computer.Network.DownloadFile(rutaHTML,nomArchivo,"","",False,100000,True)
                   
        '2) Se lee el archivo y se construye la tabla 
        dt = LeeVector(nomArchivo, nombreTabla)
        

       My.Computer.FileSystem.DeleteFile (nomArchivo) 


        Return dt

     Catch ex As Exception        
            Throw ex
     End Try
        
    End Function

    Public Shared Function LeeVector(ByVal nomArchivo As String, ByVal nombreTabla As String) As DataTable
        Dim arreglo  ()  As String
        Dim i            As Integer
        Dim numCols      As integer
        Dim dt           As New DataTable(nombreTabla)
        Using sw As StreamReader  = My.Computer.FileSystem.OpenTextFileReader(nomArchivo,System.Text.Encoding.Default ) 
            
              arreglo = sw.ReadLine().Split (",")
              numCols = arreglo.Count 

              For i = 0 to arreglo.Count  - 1  
                  'dt.Columns.Add(arreglo(i).Replace(".","") ,GetType(string))  
                  dt.Columns.Add(arreglo(i),GetType(string))  
              Next


              While Not sw.EndOfStream                   
                  arreglo = sw.ReadLine().Split (",")
                  'Array.Copy(arreglo,arregloAux,numCols) 
                  Array.Resize(arreglo,numCols )
                  dt.Rows.Add (arreglo)
                                    
              End While

           sw.Close()
         End Using
        Return dt
    End Function

    Public Shared Sub ObtenerRutaTabla(ByVal tipoEmisora As TipoEmisora, ByRef rutaHtml As String, ByRef nombreTabla As String)
        Select Case tipoEmisora 

            Case TipoEmisora.Corporativo 

                rutaHtml   = CServicioLocalSQLServerDB.ObtenerRutaHTMLCorporativo()                                                     
                nombreTabla = "Corporativo"

            Case TipoEmisora.Gubernamental 

                 rutaHtml =   CServicioLocalSQLServerDB.ObtenerRutaHTMLGubernamental()                                                  
                 nombreTabla   = "Gubernamental"

            case TipoEmisora.Bancario 

                 rutaHtml =   CServicioLocalSQLServerDB.ObtenerRutaHTMLBancaria ()                                     
                 nombreTabla = "Bancario"


            Case TipoEmisora.Notas 

                 rutaHtml   = CServicioLocalSQLServerDB.ObtenerRutaHTMLNotas ()                 
                 nombreTabla = "Notas"

            Case TipoEmisora.Eurobonos

                 rutaHtml   = CServicioLocalSQLServerDB.ObtenerRutaHTMLEurobonos()                 
                 nombreTabla = "Eurobonos"

            Case TipoEmisora.VectorValmer 

                 rutaHtml   = CServicioLocalSQLServerDB.ObtieneRutaValmer()                                  
                 nombreTabla = "VectorValmer"

        End Select
    End Sub

    Public Shared Function ObtenerDatosPorTipoEmisora(ByVal dfechaTrabajo As date, ByVal tipoEmisora As TipoEmisora ) As DataTable 

        Dim sRutaHTML     As String = ""
        Dim sParametros   As String = ""
        Dim sNomTabla     As String = ""

        Dim sFechaActual  As String 
        Dim sfechaTrabajo As String 


        Dim dt            As DataTable 
        
        Dim idError         as Integer 


        sFechaActual  = CServiciosMacrosSyBaseDB.FechaHoyDB().ToString ("yyyy-MM-dd")
        sfechaTrabajo = dfechaTrabajo.ToString ("yyyy-MM-dd") 

  
        ObtenerRutaTabla(tipoEmisora, sRutaHTML, sNomTabla)
        
 

       'Remplazar parámetro dinámico en la ruta 

        sRutaHTML = sRutaHTML.Replace("@",sfechaTrabajo)                

        Try
            dt = CAccesoHTML.ObtenerDatosDeValmeEnCSV (sRutaHTML,sNomTabla) 
        Catch ex As Exception            
            idError = CUtileria.GrabarBitacora ("LoadInstrument","ObtenerDatosPorTipoEmisora",ex.Message ) 
            CUtileria.CompletarRegBitacora (idError)
            Return Nothing                 
        End Try
        
        Return dt
    End Function


   


#End Region

#Region "Línea ReportingBancomer"
    Public Shared Function ObtieneTablaValmer(ByVal strRuta As String) As DataTable
        Dim WR As System.Net.WebRequest
        Dim Rsp As System.Net.WebResponse
        Dim arreglo() As String
        Dim columnas As Integer
        Dim dtDatos As New DataTable()

        Try
            WR = System.Net.WebRequest.Create(strRuta)
            WR.Timeout = 6000
            Rsp = WR.GetResponse()

            Dim srDatos As New StreamReader(Rsp.GetResponseStream())

            arreglo = srDatos.ReadLine().Split(",")
            columnas = arreglo.Count()

            For i = 0 To arreglo.Count - 1
                dtDatos.Columns.Add(arreglo(i), GetType(String))
            Next

            While Not srDatos.EndOfStream
                arreglo = srDatos.ReadLine().Split(",")                
                Array.Resize(arreglo, columnas)
                dtDatos.Rows.Add(arreglo)
            End While

            srDatos.Close()

            Return dtDatos
        Catch ex As System.Net.WebException
            Return Nothing
        End Try
    End Function
#End Region







End Class
